﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;

using System.Text;
using System.Reflection;
using System.Windows.Forms;
using DAO;
using DTO;
using System.Globalization;

namespace ProductManagement
{
    public partial class Report : Form
    {
        private BindingSource bsData;
        private DataTable dtReport;
        public Report()
        {
            InitializeComponent();            
        }
        private void LoadNam()
        {
            String strSql = "select distinct year(ngay)&'' as Nam from GiaoHang";
            DataTable dt = DataAccess.Query(strSql);
            DataRow aRow = dt.NewRow();
            aRow["Nam"] = "Tất cả";
            dt.Rows.InsertAt(aRow, 0);
            cboNam.DisplayMember = "Nam";
            cboNam.ValueMember = "Nam";
            cboNam.DataSource = dt;
            cboNam.SelectedValue = DateTime.Now.Year;
            if (cboNam.SelectedIndex < 0)
                cboNam.SelectedIndex = 0;
        }
        private void LoadThang()
        {
            if (cboNam != null)
            {
                int n = 12;
                if (cboNam.SelectedValue == "Tất cả")
                    n = 0;
                DataTable dt = new DataTable();
                dt.Columns.Add("MaThang", typeof(int));
                dt.Columns.Add("TenThang", typeof(String));
                DataRow aRow = dt.NewRow();
                aRow["MaThang"] = 0;
                aRow["TenThang"] = "Tất cả";
                dt.Rows.Add(aRow);
                for (int i = 0; i < n; i++)
                {
                    aRow = dt.NewRow();
                    aRow["MaThang"] = i + 1;
                    aRow["TenThang"] = (i + 1);
                    dt.Rows.Add(aRow);
                }
                cboThang.DisplayMember = "TenThang";
                cboThang.ValueMember = "MaThang";
                cboThang.DataSource = dt;
                cboThang.SelectedValue = DateTime.Now.Month;
                if (cboThang.SelectedIndex < 0)
                    cboThang.SelectedIndex = 0;
            }
        }
        private void LoadNgay()
        {
            if (cboThang.SelectedValue != null)
            {
                int[] ngayThang = new int[] { 0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
                if ((int)cboThang.SelectedValue>0 && DateTime.IsLeapYear(int.Parse(cboNam.SelectedValue.ToString())))
                    ngayThang[2] = 29;
                DataTable dt = new DataTable();
                dt.Columns.Add("MaNgay", typeof(int));
                dt.Columns.Add("TenNgay", typeof(String));
                DataRow aRow = dt.NewRow();
                aRow["MaNgay"] = 0;
                aRow["TenNgay"] = "Tất cả";
                dt.Rows.Add(aRow);
                for (int i = 0; i < ngayThang[(int)cboThang.SelectedValue]; i++)
                {
                    aRow = dt.NewRow();
                    aRow["MaNgay"] = i + 1;
                    aRow["TenNgay"] = i + 1;
                    dt.Rows.Add(aRow);
                }
                cboNgay.ValueMember = "MaNgay";
                cboNgay.DisplayMember = "TenNgay";
                cboNgay.DataSource = dt;
                if(cboNgay.SelectedIndex < 0)
                    cboNgay.SelectedIndex = 0;
            }
        }
        private void LoadReport()
        {
            String dkNam = "";            
            String dkThang = "";
            String dkNgay = "";
            String title = "Báo cáo kinh doanh ";
            String strSql = "";            
            dtReport = new DataTable();
            if ((String)cboNam.SelectedValue != "Tất cả")
            {
                dkNam = " and year(gh.Ngay)='" + cboNam.SelectedValue + "'";                
                if ((int)cboThang.SelectedValue != 0)
                {
                    dkThang = " and month(gh.Ngay)='" + cboThang.SelectedValue + "'";
                    if ((int)cboNgay.SelectedValue != 0)
                    {
                        dkNgay = " and day(gh.Ngay)='" + cboNgay.SelectedValue + "'";
                        title += cboNgay.SelectedValue + "/";                        
                    }
                    title += cboThang.SelectedValue + "/";
                }
                title += cboNam.SelectedValue;
            }
            else
            {
                try
                {
                    strSql = "select min(Ngay) as NgayMin, max(Ngay) as NgayMax from PhanPhoi";
                    dtReport.Rows.Clear();
                    dtReport = DataAccess.Query(strSql);
                    title += "từ " + ((DateTime)dtReport.Rows[0]["NgayMin"]).Month + "/" + ((DateTime)dtReport.Rows[0]["NgayMin"]).Year +
                    " đến " + ((DateTime)dtReport.Rows[0]["NgayMax"]).Month + "/" + ((DateTime)dtReport.Rows[0]["NgayMax"]).Year;
                }
                catch (Exception ex) { }
            }
            strSql = "select dl.ID as MaDaiLy, dl.TenDaiLy as DaiLy, dl.Quan as Quan, LoiNhuanGas, PhiDoiVo, (LoiNhuanGas-PhiDoiVo) as ThucLai " +
                            "from (SELECT DaiLy, sum(SoLuong*(GiaBan-GiaGoc)) as LoiNhuanGas from PhanPhoi ppp, GiaoHang gh where ppp.MaGiaoHang=gh.ID" + dkNam + dkThang + dkNgay + " group by DaiLy) pp," +
                             "(select DaiLy, sum(SoLuong*th.PhiDoiVo) as PhiDoiVo from ThuHoi th, GiaoHang gh where th.MaGiaoHang=gh.ID " + dkNam + dkThang + dkNgay + " group by DaiLy) th, DaiLy dl " + 
                                "where dl.ID=pp.DaiLy and pp.DaiLy = th.DaiLy";

            dtReport = DataAccess.Query(strSql);
            if (dtReport.Rows.Count == 0)
                statusReportMess.Text = "Không có dữ liệu";
            else
                statusReportMess.Text = "Đọc dữ liệu thành công";
            dtReport.Columns.Add("STT");
            double tongLai = 0;
            double tongPhiDoiVo = 0;
            double tongThucLai = 0;
            for (int i = 0; i < dtReport.Rows.Count; i++)
            {
                dtReport.Rows[i]["STT"] = i + 1;
                
                tongLai += (double)dtReport.Rows[i]["LoiNhuanGas"];
                tongPhiDoiVo += (double)dtReport.Rows[i]["PhiDoiVo"];
                tongThucLai += (double)dtReport.Rows[i]["ThucLai"];
            }                     
            dgvReport.AutoGenerateColumns = false;
            bsData = new BindingSource(dtReport, null);
            dgvReport.DataSource = bsData;
            lblGirdReport.Text = title;
            TinhTong();
        }

        private void TinhTong()
        {
            double tongLai = 0;
            double tongPhiDoiVo = 0;
            double tongThucLai = 0;
            foreach (DataGridViewRow row in dgvReport.Rows)
            {
                if (row.Visible = true)
                {
                    tongLai += (double)row.Cells["LoiNhuanGas"].Value;
                    tongPhiDoiVo += (double)row.Cells["PhiDoiVo"].Value;
                    tongThucLai += (double)row.Cells["ThucLai"].Value;
                }
            }
            txtTongLoiNhuanGas.Text = tongLai.ToString("n0") + " VND";
            txtTongLoiNhuanThuc.Text = tongThucLai.ToString("n0") + " VND";
            txtTongPhiDoiVo.Text = tongPhiDoiVo.ToString("n0") + " VND";

        }
        private void Report_Load(object sender, EventArgs e)
        {
            try
            {
                LoadNam();
                LoadThang();
                LoadNgay();
                LoadReport();
                lblNgay.Text = "Hôm nay: " + DateTime.Now.ToString("dd/MM/yyyy");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Không thể load form\r\n" + ex.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);                
            }
        }

        private void dgvReport_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            try
            {
                if (e.RowIndex >= 0 && e.RowIndex < dgvReport.Rows.Count - 1)
                {
                    dgvReport.Rows[e.RowIndex].Selected = true;
                    int maDaiLy = (int)dgvReport.Rows[e.RowIndex].Cells["MaDaiLy"].Value;                    
                    int ng, th, na;
                    if (cboNam.SelectedValue == "Tất cả")
                        na = 0;
                    else
                        na = int.Parse((String)cboNam.SelectedValue);
                    th = (int)cboThang.SelectedValue;
                    ng = (int)cboNgay.SelectedValue;
                    ReportChiTiet frm = new ReportChiTiet(maDaiLy, (String)dgvReport.Rows[e.RowIndex].Cells["DaiLy"].Value, ng, th, na);
                    frm.ShowDialog();
                    Report_Load(sender, null);
                }
            }
            catch (Exception ex)
            {
            }
        }

        private void btnChiTiet_Click(object sender, EventArgs e)
        {
            try
            {
                if (dgvReport.CurrentCellAddress.Y >= 0 && dgvReport.CurrentCellAddress.Y < dgvReport.Rows.Count - 1)
                {
                    int maDaiLy = (int)dgvReport.Rows[dgvReport.CurrentCellAddress.Y].Cells["MaDaiLy"].Value;
                    int ng, th, na;
                    if (cboNam.SelectedValue == "Tất cả")
                        na = 0;
                    else
                        na = int.Parse((String)cboNam.SelectedValue);
                    th = (int)cboThang.SelectedValue;
                    ng = (int)cboNgay.SelectedValue;
                    ReportChiTiet frm = new ReportChiTiet(maDaiLy, (String)dgvReport.Rows[dgvReport.CurrentCellAddress.Y].Cells["DaiLy"].Value, ng, th, na);
                    frm.ShowDialog();
                    Report_Load(sender, null);
                }
            }
            catch (Exception ex)
            {
            }
        }

        private void dgvReport_Sorted(object sender, EventArgs e)
        {
            System.Drawing.Font font = new Font("Arial", 9.75f, FontStyle.Bold);
            for (int i = 1; i < dgvReport.Rows[0].Cells.Count; i++)
                dgvReport.Rows[dgvReport.Rows.Count - 1].Cells[i].Style.Font = font;
            dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["DaiLy"].Style.Alignment = DataGridViewContentAlignment.MiddleRight;
        }

        private void dgvReport_SortCompare(object sender, DataGridViewSortCompareEventArgs e)
        {
          
        }

        private void btnThuVo_Click(object sender, EventArgs e)
        {
            ThuVo frm = new ThuVo();
            frm.ShowDialog(this);
            
            Report_Load(sender, e);
        }

        private void btnPhanPhoi_Click(object sender, EventArgs e)
        {
            frmPhanPhoi frm = new frmPhanPhoi();
            frm.ShowDialog();
            Report_Load(sender, e);
        }

        private void dgvReport_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.RowIndex >= 0 && e.RowIndex < dgvReport.Rows.Count && e.ColumnIndex == 2)
                {
                    dgvReport.Rows[e.RowIndex].Selected = true;
                    int maDaiLy = (int)dgvReport.Rows[e.RowIndex].Cells["MaDaiLy"].Value;
                    int ng, th, na;
                    if (cboNam.SelectedValue == "Tất cả")
                        na = 0;
                    else
                        na = int.Parse((String)cboNam.SelectedValue);
                    th = (int)cboThang.SelectedValue;
                    ng = (int)cboNgay.SelectedValue;
                    ReportChiTiet frm = new ReportChiTiet(maDaiLy, (String)dgvReport.Rows[e.RowIndex].Cells["DaiLy"].Value, ng, th, na);
                    frm.ShowDialog();
                    Report_Load(sender, null);
                }
            }
            catch (Exception ex)
            {
            }
        }
        private void cboThang_SelectionChangeCommitted(object sender, EventArgs e)
        {
            try
            {
                LoadNgay();
                LoadReport();
            }
            catch (Exception ex) { }
        }

        private void cboNgay_SelectionChangeCommitted(object sender, EventArgs e)
        {
            try
            {
                LoadReport();
            }
            catch (Exception ex) { }
        }

        private void cboNam_SelectionChangeCommitted(object sender, EventArgs e)
        {
            try
            {
                LoadThang();
                LoadReport();
            }
            catch (Exception ex) { }
        }

        private void loạiGasToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmLoaiGas frm = new frmLoaiGas();
            frm.Show();
        }

        private void cácĐạiLýToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmDaiLy frm = new frmDaiLy();
            frm.Show();
        }

        private void cboQuanFilter_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void exportToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                Microsoft.Office.Interop.Excel.Workbook wBook;
                Microsoft.Office.Interop.Excel.Worksheet wSheet;
                wBook = excel.Workbooks.Add("");
                wSheet = (Microsoft.Office.Interop.Excel.Worksheet)wBook.ActiveSheet;
                String title = "Báo cáo kinh doanh: ";
                if ((String)cboNam.SelectedValue != "Tất cả")
                {
                    if ((int)cboThang.SelectedValue != 0)
                    {
                        if ((int)cboNgay.SelectedValue != 0)
                        {
                            title += cboNgay.SelectedValue + "/";
                        }
                        title += cboThang.SelectedValue + "/";
                    }
                    title += cboNam.SelectedValue;
                }
                excel.Cells[1, 1] = title;

                excel.Cells[2, 1] = "STT";
                excel.Cells[2, 2] = "Đại lý";
                excel.Cells[2, 3] = "Quận";
                excel.Cells[2, 4] = "Lợi nhuận Gas";
                excel.Cells[2, 5] = "Phí đổi vỏ";
                excel.Cells[2, 6] = "Lợi nhuận thực"; 
                ((Microsoft.Office.Interop.Excel.Range)excel.Cells[2, 1]).EntireRow.Font.Bold = true;
                DataTable dt = bsData.DataSource as DataTable;
                int i = 3;
                foreach (DataRow dr in dt.Rows)
                {
                    excel.Cells[i, 1] = dr["STT"];
                    excel.Cells[i, 2] = dr["DaiLy"];
                    excel.Cells[i, 3] = dr["Quan"];
                    excel.Cells[i, 4] = dr["LoiNhuanGas"];
                    excel.Cells[i, 5] = dr["PhiDoiVo"];
                    excel.Cells[i, 6] = dr["ThucLai"];
                    //if(i == dt.Rows.Count + 1)
                    //    ((Microsoft.Office.Interop.Excel.Range)excel.Cells[i, 1]).EntireRow.Font.Bold = true;
                    i++;
                }
                excel.Cells[i, 3] = "Tổng: ";
                excel.Cells[i, 4] = txtTongLoiNhuanGas.Text;
                excel.Cells[i, 5] = txtTongPhiDoiVo.Text;
                excel.Cells[i, 6] = txtTongLoiNhuanThuc.Text;
                wSheet.Rows.RowHeight = 20;
                wSheet.Rows.Font.Size = 10;
                wSheet.Rows.Font.Name = "Arial";                
                wSheet.Columns.AutoFit();               
                MessageBox.Show("Xuất dữ liệu thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                SaveFileDialog dlg = new SaveFileDialog();
                dlg.Title = "Save as";
                dlg.Filter = "Excel 97-2003 Workbook|*.xls|All files|*.*";
                if (dlg.ShowDialog(this) == DialogResult.OK)
                {
                    String strFileName = dlg.FileName;
                    try
                    {
                        System.IO.FileStream fileTemp = System.IO.File.OpenWrite(strFileName);
                        fileTemp.Close();
                    }
                    catch (Exception ex)
                    {
                    }
                    if (System.IO.File.Exists(strFileName))
                        System.IO.File.Delete(strFileName);
                    wBook.SaveAs(strFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    excel.Workbooks.Open(strFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    excel.Visible = true;
                }
                else
                {
                    wBook.Close(false, Type.Missing, Type.Missing);
                    excel.Quit();
                }
            }
            catch (Exception ex) { MessageBox.Show("Xuất dữ liệu không thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning); }
        }

        private void gasToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmLoaiGas frm = new frmLoaiGas();
            frm.Show();
        }

        private void đạiLýToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmDaiLy frm = new frmDaiLy();
            frm.Show();
        }

        private void giaoHàngToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmQLGiaoHang frm = new frmQLGiaoHang(false);
            frm.ShowDialog(this);
        }

        private void xeGiaHàngVềToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmQLGiaoHang frm = new frmQLGiaoHang(true);
            frm.ShowDialog(this);
        } 

        private void loạiGasToolStripMenuItem_Click_1(object sender, EventArgs e)
        {
            frmLoaiGas frm = new frmLoaiGas();
            frm.ShowDialog(this);
        }

        private void thốngKêToolStripMenuItem_Click(object sender, EventArgs e)
        {

        }

        private void nạpGasToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            frmNapGas frm = new frmNapGas();
            frm.ShowDialog(this);
        }

        private void muaVỏToolStripMenuItem_Click(object sender, EventArgs e)
        {
            MuaVo frm = new MuaVo();
            frm.ShowDialog(this);
        }

        private void bánVỏToolStripMenuItem_Click(object sender, EventArgs e)
        {
            BanVo frm = new BanVo();
            frm.ShowDialog(this);
        }

        private void đổiVỏToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DoiVo frm = new DoiVo();
            frm.ShowDialog(this);
        }

        private void bìnhGasToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmBinhGas frm = new frmBinhGas();
            frm.ShowDialog();
        }

        private void vỏBìnhGasToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmVoBinhGas frm = new frmVoBinhGas();
            frm.ShowDialog();
            if (frm.openBinhGas == true)
            {
                frmBinhGas form = new frmBinhGas();
                form.ShowDialog();
            }
        }

        private void phânPhốiGasToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmQLPhanPhoi frm = new frmQLPhanPhoi();
            frm.ShowDialog();
            OnLoad(e);
        }

        private void thuHồiVỏBìnhToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmQLThuHoi frm = new frmQLThuHoi();
            frm.ShowDialog();
            OnLoad(e);
        }

        private void xuấtKhoToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmGiaoHang frm = new frmGiaoHang();
            frm.ShowDialog();
            OnLoad(e);
        }

        private void xeGiaHàngVềToolStripMenuItem_Click_1(object sender, EventArgs e)
        {
            frmQLGiaoHang frm = new frmQLGiaoHang(false);
            frm.ShowDialog();
            OnLoad(e);
        }

        private void quảnLýGiaoHàngToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmQLGiaoHang frm = new frmQLGiaoHang(true);
            frm.ShowDialog();
            OnLoad(e);
        }

        private void cấuHìnhToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //
        }

        private void hướngDẫnSửDụngToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Help.ShowHelp(this, "usermanual.chm");
        }

        private void aboutToolStripMenuItem_Click(object sender, EventArgs e)
        {
            AboutBox1 dlg = new AboutBox1();
            dlg.ShowDialog();
        }

        private void quảnLýGiaoHàngToolStripMenuItem_Click_1(object sender, EventArgs e)
        {
            frmQLGiaoHang frm = new frmQLGiaoHang(true);
            frm.ShowDialog();
            OnLoad(e);
        }

        private void dgvReport_ColumnHeaderCellChanged(object sender, DataGridViewColumnEventArgs e)
        {
            TinhTong();
        }

        private void dgvReport_Paint(object sender, PaintEventArgs e)
        {
            TinhTong();
        }
    }
}
